---
id: hcl-schema
title: HCL Schema
slug: /atlas-schema/hcl
---

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

Atlas schemas can be defined in SQL, external ORMs and programs, or by using the [Atlas HCL](/guides/ddl.md#hcl) language.
The HCL-based language allows developers to describe database schemas in a declarative manner, and it supports all SQL
features supported by Atlas. The main advantages of using HCL are that it enables developers to manage their database
schemas like regular code, facilitates sharing and reusing files between projects, allows variable injection, and
provides the ability to attach annotations to objects, such as PII or sensitive data.

## Schema

The `schema` object describes a database schema. A `DATABASE` in MySQL and SQLite, or a `SCHEMA` in PostgreSQL.
An HCL file can contain 1 or more schema objects.

<Tabs defaultValue="mysql">
<TabItem label="MySQL" value="mysql">

In MySQL and MariaDB, the `schema` resource can contain the `charset` and `collate` attributes. Read more about them
in [MySQL](https://dev.mysql.com/doc/refman/8.0/en/charset.html) or
[MariaDB](https://mariadb.com/kb/en/setting-character-sets-and-collations/) websites.

```hcl
# Schema with attributes.
schema "market" {
  charset = "utf8mb4"
  collate = "utf8mb4_0900_ai_ci"
  comment = "A schema comment"
}

# Schema without attributes.
schema "orders" {}
```

</TabItem>
<TabItem label="PostgreSQL" value="postgres">

```hcl
schema "public" {
  comment = "A schema comment"
}

schema "private" {}
```

</TabItem>
<TabItem label="SQLite" value="sqlite">

Atlas does not support [attached databases](https://www.sqlite.org/lang_attach.html), and support only the default
database (i.e. `main`).

```hcl
schema "main" {}
```

</TabItem>
<TabItem value="sqlserver" label="SQL Server">

```hcl
schema "dbo" {
  comment = "A schema comment"
}

schema "private" {}
```

</TabItem>
<TabItem value="clickhouse" label="ClickHouse">

In Clickhouse, the `schema` resource can contain the `engine` attribute. If not specified, the default engine depends on ClickHouse settings.
Use `sql()` to specify the engine in advanced cases.

Read more about database engines in [ClickHouse documentation](https://clickhouse.com/docs/en/engines/database-engines).

```hcl
schema "default" {
  engine = sql("Replicated('/clickhouse/databases/default', '{shard}', '{replica}')")
}

schema "atomic" {
  engine = Atomic
}
```

</TabItem>
</Tabs>

## Table

A `table` describes a table in a SQL database. A table hold its columns, indexes, constraints, and additional attributes
that are supported by the different drivers.

```hcl
table "users" {
  schema = schema.public
  column "id" {
    type = int
  }
  column "name" {
    type = varchar(255)
  }
  column "manager_id" {
    type = int
  }
  primary_key {
    columns = [
      column.id
    ]
  }
  index "idx_name" {
    columns = [
      column.name
    ]
    unique = true
  }
  foreign_key "manager_fk" {
    columns = [column.manager_id]
    ref_columns = [column.id]
    on_delete = CASCADE
    on_update = NO_ACTION
  }
}
```

### Check

A `check` is a child resource of a `table` that describes a `CHECK` constraint.

```hcl
table "products" {
    column "price" {
        type = float
    }
    check "positive price" {
	    expr = "price > 0"
    }
}
```

### Partitions

Table partitioning refers to splitting logical large tables into smaller physical ones.

:::note
Partitions are currently supported only by the PostgreSQL driver. Support for the remaining drivers will be added in future versions.
:::

```hcl
table "logs" {
  schema = schema.public
  column "date" {
    type = date
  }
  column "text" {
    type = integer
  }
  partition {
    type = RANGE
    columns = [column.date]
  }
}

table "metrics" {
  schema = schema.public
  column "x" {
    type = integer
  }
  column "y" {
    type = integer
  }
  partition {
    type = RANGE
    by {
      column = column.x
    }
    by {
      expr = "floor(y)"
    }
  }
}
```

### Storage Engine

The `engine` attribute allows for overriding the default storage engine of the table. Supported by MySQL and MariaDB.

```hcl
table "users" {
  schema = schema.public
  // highlight-next-line
  engine = MyISAM
}

table "posts" {
  schema = schema.public
  // highlight-next-line
  engine = InnoDB
}

table "orders" {
  schema = schema.public
  // highlight-next-line
  engine = "MyRocks"
}
```

### Table Qualification

In some cases, an Atlas DDL document may contain multiple tables of the same name. This usually happens
when the same table name appears in two different schemas. In these cases, the table names must be
disambiguated by using resource [qualifiers](/guides/ddl.md#qualifiers). The following document describes a
database that contains two schemas named `a` and `b`, and both of them contain a table named `users`.

```hcl
schema "a" {}
schema "b" {}

table "a" "users" {
  schema = schema.a
  // .. columns
}
table "b" "users" {
  schema = schema.b
  // .. columns
}
```

## View

A `view` is a virtual table in the database, defined by a statement that queries rows from one or more existing
tables or views.

:::info LOGIN REQUIRED
Views are currently available to logged-in users only. To use this feature, run:
```
atlas login
```
:::

```hcl
view "clean_users" {
  schema = schema.public
  column "id" {
    type = int
  }
  column "name" {
    type = text
  }
  as         = <<-SQL
  SELECT u.id, u.name
    FROM ${table.users.name} AS u
    JOIN ${view.active_users.name} AS au USING (id)
  SQL
  depends_on = [table.users, view.t1]
  comment    = "A view to active users without sensitive data"
}

view "comedies" {
  schema = schema.public
  column "id" {
    type = int
  }
  column "name" {
    type = text
  }
  as           = "SELECT id, name FROM films WHERE kind = 'Comedy'"
  depends_on   = [table.films]
  check_option = CASCADED
}
```

## Materialized View

A `materialized` view is a table-like structure that holds the results of a query. Unlike a regular view, the results of
a materialized view are stored in the database and can be refreshed periodically to reflect changes in the underlying data.

:::info LOGIN REQUIRED
Materialized views are currently available to logged-in users only. To use this feature, run:
```
atlas login
```
:::

<Tabs>
<TabItem label="PostgreSQL" value="postgres">

```hcl
materialized "mat_view" {
  schema = schema.public
  column "total" {
    null = true
    type = numeric
  }
  index "idx_expr" {
    unique = true
    on {
      expr = "((total > (0)::numeric))"
    }
  }
  index "idx_pred" {
    unique  = true
    columns = [column.total]
    where   = "(total < (0)::numeric)"
  }
  as         = <<-SQL
   SELECT sum(total) AS total
     FROM m1;
  SQL
  depends_on = [materialized.m1]
}
```

</TabItem>
<TabItem label="Clickhouse" value="clickhouse">

When creating materialized views with `TO [db.]table`, 
the view will be created with the same structure as the table or view specified in the `TO` clause.
```hcl
materialized "mat_view" {
  schema     = schema.public
  to         = table.dest
  as         = "SELECT * FROM table.src"
  depends_on = [table.src]
}
```

The `engine` and `primary_key` attributes are required if the `TO` clause is not specified.
In this syntax, `populate` can be used for the first time to populate the materialized view.

```hcl
materialized "mat_view" {
  schema = schema.public
  engine = MergeTree
  column "id" {
    type = UInt32
  }
  column "name" {
    type = String
  }
  primary_key {
    columns = [column.id]
  }
  as         = "SELECT * FROM table.src"
  populate   = true
  depends_on = [table.src]
}
```

:::info
Note that modifying the materialized view structure 
after the initial creation is not supported by Atlas currently.
:::

</TabItem>
</Tabs>

## Column

A `column` is a child resource of a `table`.

```hcl
column "name" {
  type = text
  null = false
}

column "age" {
  type = integer
  default = 42
}

column "active" {
  type = tinyint(1)
  default = true
}
```

#### Properties

| Name    | Kind      | Type                    | Description                                                |
|---------|-----------|-------------------------|------------------------------------------------------------|
| null    | attribute | bool                    | Defines whether the column is nullable.                    |
| type    | attribute | *schemahcl.Type         | Defines the type of data that can be stored in the column. |
| default | attribute | *schemahcl.LiteralValue | Defines the default value of the column.                   |

### Generated Columns

Generated columns are columns whose their values are computed using other columns or by deterministic expressions.

<Tabs defaultValue="mysql">
<TabItem label="MySQL" value="mysql">

```hcl
table "users" {
  schema = schema.test
  column "a" {
    type = int
  }
  column "b" {
    type = int
    # In MySQL, generated columns are VIRTUAL by default.
    as = "a * 2"
  }
  column "c" {
    type = int
    as {
      expr = "a * b"
      type = STORED
    }
  }
}
```

</TabItem>
<TabItem label="PostgreSQL" value="postgres">

```hcl
table "users" {
  schema = schema.test
  column "a" {
    type = int
  }
  column "b" {
    type = int
    # In PostgreSQL, generated columns are STORED by default.
    as = "a * 2"
  }
  column "c" {
    type = int
    as {
      expr = "a * b"
      type = STORED
    }
  }
}
```

</TabItem>
<TabItem label="SQLite" value="sqlite">

```hcl
table "users" {
  schema = schema.test
  column "a" {
    type = int
  }
  column "b" {
    type = int
    # In SQLite, generated columns are VIRTUAL by default.
    as = "a * 2"
  }
  column "c" {
    type = int
    as {
      expr = "a * b"
      type = STORED
    }
  }
}
```

</TabItem>
<TabItem value="sqlserver" label="SQL Server">

```hcl
table "users" {
  schema = schema.test
  column "a" {
    type = int
  }
  column "b" {
    type = int
    as = "a * 2"
  }
  column "c" {
    type = int
    as {
      expr = "a * b"
      # In SQLServer, computed columns are non-PERSISTED by default.
      type = PERSISTED
    }
  }
}
```

</TabItem>
</Tabs>

:::info
Note, it is recommended to use the [`--dev-url`](../concepts/dev-database) option when generated columns are used.
:::

## Column Types

The SQL dialects supported by Atlas (Postgres, MySQL, MariaDB, and SQLite) vary in
the types they support. At this point, the Atlas DDL does not attempt to abstract
away the differences between various databases. This means that the schema documents
are tied to a specific database engine and version. This may change in a future version
of Atlas as we plan to add "Virtual Types" support. This section lists the various
types that are supported in each database.

For a full list of supported column types, [click here](hcl-types.md).

## Primary Key

A `primary_key` is a child resource of a `table`, and it defines the table's
primary key.

#### Example

```hcl
primary_key {
  columns = [column.id]
}
```

#### Properties

| Name    | Kind      | Type                     | Description                                                    |
|---------|-----------|--------------------------|----------------------------------------------------------------|
| columns | resource  | reference (list)         | A list of references to columns that comprise the primary key. |

## Foreign Key

Foreign keys are child resources of a `table`, and it defines some columns in the table
as references to columns in other tables.

#### Example

```hcl title="schema.hcl" {18-19}
table "users" {
  schema = schema.public
  column "id" {
    type = integer
  }
  primary_key {
    columns = [column.id]
  }
}

table "orders" {
  schema = schema.market
  // ...
  column "owner_id" {
    type = integer
  }
  foreign_key "owner_id" {
    columns     = [column.owner_id]
    ref_columns = [table.users.column.id]
    on_update   = NO_ACTION
    on_delete   = NO_ACTION
  }
}
```

#### Referencing Qualified Tables

If a foreign key references a column in a [qualified](#table-qualification) table, it is referenced
using `table.<qualifier>.<table_name>.column.<column_name>`:

```hcl title="schema.hcl" {18-19}
table "public" "users" {
  schema = schema.public
  column "id" {
    type = integer
  }
  primary_key {
    columns = [column.id]
  }
}

table "admin" "users" {
  schema = schema.admin
  // ...
  column "external_id" {
    type = integer
  }
  foreign_key "external_id" {
    columns     = [column.external_id]
    ref_columns = [table.admin.users.column.id]
    on_update   = NO_ACTION
    on_delete   = NO_ACTION
  }
}
```

#### Properties

| Name        | Kind      | Type                   | Description                               |
|-------------|-----------|------------------------|-------------------------------------------|
| columns     | attribute | reference (list)       | The columns that reference other columns. |
| ref_columns | attribute | reference (list)       | The referenced columns.                   |
| on_update   | attribute | schema.ReferenceOption | Defines what to do on update.             |
| on_delete   | attribute | schema.ReferenceOption | Defines what to do on delete.             |

## Index

Indexes are child resources of a `table`, and it defines an index on the table.

#### Example

```hcl
index "idx_name" {
    columns = [
      column.name
    ]
    unique = true
}

index "idx_name" {
    on {
        column = column.rank
    }
    on {
        column = column.score
        desc = true
    }
    unique = true
}

index "idx_name" {
  type = GIN
  columns = [column.data]
}

index "idx_range" {
  type = BRIN
  columns = [column.range]
  page_per_range = 128
}

index "idx_include" {
  columns = [column.range]
  include = [column.version]
}

index "idx_operator_class" {
  type = GIN
  on {
    column = column.j
    ops    = jsonb_path_ops
  }
}

index "index_parser" {
  type    = FULLTEXT
  columns = [column.text]
  parser  = ngram
}

index "index_nulls_not_distinct" {
  unique         = true
  columns        = [column.text]
  nulls_distinct = false
}
```

#### Properties

| Name      | Kind      | Type                    | Description                                                    |
|-----------|-----------|-------------------------|----------------------------------------------------------------|
| unique    | attribute | boolean                 | Defines whether a uniqueness constraint is set on the index.   |
| type      | attribute | IndexType (enum)        | Defines the index type. e.g. `HASH`, `GIN`, `FULLTEXT`.        |
| columns   | attribute | reference (list)        | The columns that comprise the index.                           |
| on        | resource  | schema.IndexPart (list) | The index parts that comprise the index.                       |
| options   | attribute | schema.Attr             | Additional driver specific attributes. e.g. `page_per_range`   |

### Index Expressions

Index expressions allow setting indexes over functions or computed expressions. Supported by PostgreSQL, SQLite and
MySQL8.

```hcl {9-16}
table "t" {
  schema = schema.test
  column "c1" {
    type = int
  }
  column "c2" {
    type = int
  }
  index "i" {
    on {
      expr = "c1 - c2"
    }
    on {
      expr = "c2 - c1"
    }
  }
}
```

:::info
Note, it is recommended to use the [`--dev-url`](../concepts/dev-database) option when index expressions are used.
:::

### Partial Indexes

[Partial indexes](https://www.postgresql.org/docs/current/indexes-partial.html) allow setting indexes over subset of
the table. Supported by PostgreSQL and SQLite.

```hcl {11}
table "t" {
  schema = schema.public
  column "b" {
    type = bool
  }
  column "c" {
    type = int
  }
  index "i" {
    columns = [column.c]
    where = "b AND c > 0"
  }
}
```

:::info
Note, it is recommended to use the [`--dev-url`](../concepts/dev-database) option when partial indexes are used.
:::

### Index Prefixes

[Index prefixes](https://dev.mysql.com/doc/refman/8.0/en/column-indexes.html#column-indexes-prefix) allow setting an index
on the first `N` characters of string columns. Supported by MySQL and MariaDB.

```hcl {9}
table "users" {
  schema = schema.test
  column "name" {
    type = varchar(255)
  }
  index "user_name" {
    on {
      column = column.name
      prefix = 128
    }
  }
}
```

## Trigger

:::info BETA FEATURE
Triggers are currently in beta and available to logged-in users only. To use this feature, run:
```
atlas login
```
:::

The `trigger` block allows defining SQL triggers in HCL format.


<Tabs>
<TabItem label="PostgreSQL" value="postgres">

```hcl
function "audit_orders" {
  schema = schema.public
  lang   = PLpgSQL
  return = trigger
  as     = <<-SQL
  BEGIN
    INSERT INTO orders_audit(order_id, operation) VALUES (NEW.order_id, TG_OP);
    RETURN NEW;
  END;
  SQL
}
trigger "trigger_orders_audit" {
  on = table.orders
  after {
    insert    = true
    update_of = [table.orders.column.amount]
  }
  execute {
    function = function.audit_orders
  }
}
```

</TabItem>
<TabItem label="MySQL" value="mysql">

```hcl
trigger "after_orders_insert" {
  on = table.orders
  after {
    insert = true
  }
  as = <<-SQL
  BEGIN
      INSERT INTO orders_audit(order_id, changed_at, operation)
      VALUES (NEW.order_id, NOW(), 'INSERT');
  END
  SQL
}
trigger "after_orders_update" {
  on = table.orders
  after {
    update = true
  }
  as = <<-SQL
  BEGIN
      INSERT INTO orders_audit(order_id, changed_at, operation)
      VALUES (NEW.order_id, NOW(), 'UPDATE');
  END
  SQL
}
```

</TabItem>
<TabItem label="SQLite" value="sqlite">

```hcl
trigger "after_orders_insert" {
  on = table.orders
  after {
    insert = true
  }
  as = <<-SQL
  BEGIN
      INSERT INTO orders_audit(order_id, operation) VALUES (NEW.order_id, 'INSERT');
  END
  SQL
}
trigger "after_orders_update" {
  on = table.orders
  after {
    update_of = [table.orders.column.amount]
  }
  as = <<-SQL
  BEGIN
      INSERT INTO orders_audit(order_id, operation) VALUES (NEW.order_id, 'UPDATE');
  END
  SQL
}
```

</TabItem>
<TabItem label="SQL Server" value="sqlserver">

```hcl
trigger "t1_trg" {
  on = table.orders
  after {
    insert = true
    update = true
    delete = true
  }
  as = <<-SQL
  BEGIN
    SET NOCOUNT ON;
    DECLARE @c INT;
    SELECT @c = COUNT(*) FROM [dbo].[orders];
    IF @c > 1000
      RAISERROR('Too many rows in orders', 16, 1);
  END
  SQL
}
trigger "t2_trg" {
  on = table.customers
  instead_of {
    insert = true
  }
  as = <<-SQL
  BEGIN
    SET NOCOUNT ON;
    INSERT INTO [dbo].[customers] ([name])
    SELECT [ins].[name]
    FROM [inserted] [ins]
    WHERE [ins].[name] NOT IN (
      SELECT [name] FROM [dbo].[blacklist_customers]
    );
  END
  SQL
}
```

</TabItem>
</Tabs>

### Computed Triggers

To configure the same trigger for multiple tables/views, users can utilize the `for_each` meta-argument. By setting it
up, a `trigger` block will be computed for each item in the provided value. Note that `for_each` accepts either a `map`
or a `set` of references.

```hcl title="schema.pg.hcl" {2-3}
trigger "audit_log_trigger" {
  for_each = [table.users, table.orders, table.payments]
  on       = each.value
  after {
    insert = true
    update = true
    delete = true
  }
  execute  {
    function = function.audit_log_table
  }
}
```

## Function

:::info BETA FEATURE
Functions are currently in beta and available to logged-in users only. To use this feature, run:
```
atlas login
```
:::

The `function` block allows defining SQL functions in HCL format.

<Tabs>
<TabItem label="PostgreSQL" value="postgres">

```hcl
function "positive" {
  schema = schema.public
  lang   = SQL
  arg "v" {
    type = integer
  }
  return = boolean
  as     = "SELECT v > 0"
}

function "sql_body1" {
  schema = schema.public
  lang   = SQL
  arg "v" {
    type = integer
  }
  return = integer
  as = <<-SQL
   BEGIN ATOMIC
    SELECT v;
   END
  SQL
}

function "sql_body2" {
  schema = schema.public
  lang   = SQL
  arg {
    type = integer
  }
  return     = integer
  as         = "RETURN $1"
  volatility = IMMUTABLE // STABLE | VOLATILE
  leakproof  = true      // NOT LEAKPROOF | LEAKPROOF
  strict     = true      // (CALLED | RETURNS NULL) ON NULL INPUT
}
```

</TabItem>
<TabItem label="MySQL" value="mysql">

```hcl
function "add2" {
  schema = schema.public
  arg "a" {
    type = int
  }
  arg "b" {
    type = int
  }
  return        = int
  as            = "return a + b"
  deterministic = true   // NOT DETERMINISTIC | DETERMINISTIC
  data_access   = NO_SQL // CONTAINS_SQL | NO_SQL | READS_SQL_DATA | MODIFIES_SQL_DATA
}

function "f1" {
  schema = schema.public
  arg "x" {
    type = int
  }
  return = int
  as     = <<-SQL
   BEGIN
      INSERT INTO t1 VALUES (RAND(x));
      RETURN x+2;
   END
  SQL
}
```

</TabItem>
<TabItem value="sqlserver" label="SQL Server">

```hcl
function "fn_return_scalar" {
  schema = schema.dbo
  lang   = SQL
  arg "@a" {
    type = int
  }
  arg "@b" {
    type    = int
    default = 1
  }
  return       = int
  as           = <<-SQL
  BEGIN
    RETURN @a * @a + @b * @b
  END
  SQL
  schema_bound = true         // SCHEMABINDING
  null_call    = RETURNS_NULL // (RETURNS NULL | CALLED) ON NULL INPUT
  inline       = true         // INLINE = { (OFF | ON) }
}

function "fn_return_inline" {
  schema = schema.dbo
  lang   = SQL
  arg "@a" {
    type = int
  }
  arg "@b" {
    type    = int
    default = 1
  }
  return = sql("table")
  as     = "RETURN SELECT @a as [a], @b as [b], (@a+@b)*2 as [p], @a*@b as [s]"
}

function "fn_return_table" {
  schema = schema.dbo
  lang   = SQL
  arg "@a" {
    type = int
  }
  arg "@b" {
    type    = int
    default = 1
  }
  return_table "@t1" {
    column "c1" {
      null = false
      type = int
    }
    column "c2" {
      null = false
      type = nvarchar(255)
    }
    column "c3" {
      null    = true
      type    = nvarchar(255)
      default = sql("N'G'")
    }
    column "c4" {
      null = false
      type = int
    }
    primary_key {
      columns = [column.c1]
    }
    index {
      unique       = true
      nonclustered = true
      on {
        desc   = true
        column = column.c3
      }
      on {
        column = column.c4
      }
    }
    index {
      unique       = true
      nonclustered = true
      on {
        column = column.c2
      }
      on {
        desc   = true
        column = column.c3
      }
    }
    index "idx" {
      columns      = [column.c2]
      nonclustered = true
    }
    check {
      expr = "([c4]>(0))"
    }
  }
  as = <<-SQL
  BEGIN
    INSERT @t1
    SELECT 1 AS [c1], 'A' AS [c2], NULL AS [c3], @a * @a + @b AS [c4];
    RETURN
  END
  SQL
}
```
</TabItem>
</Tabs>

## Procedure

:::info BETA FEATURE
Procedures are currently in beta and available to logged-in users only. To use this feature, run:
```
atlas login
```
:::

The `procedure` block allows defining SQL procedure in HCL format.

<Tabs>
<TabItem label="PostgreSQL" value="postgres">

```hcl
procedure "proc" {
  schema = schema.public
  lang   = SQL
  arg "a" {
    type = integer
  }
  arg "b" {
    type = text
  }
  arg "c" {
    type    = integer
    default = 100
  }
  as = <<-SQL
   INSERT INTO t1 VALUES(a, b);
   INSERT INTO t2 VALUES(c, b);
  SQL
}
```

</TabItem>
<TabItem label="MySQL" value="mysql">

```hcl
procedure "p1" {
  schema = schema.public
  arg "x" {
    type = varchar(10)
  }
  as            = "INSERT INTO t1 VALUES(x)"
  comment       = "A procedure comment"
  deterministic = true
}

procedure "p2" {
  schema = schema.public
  arg "x" {
    type    = char(10)
    mode    = INOUT
    charset = "latin1"
  }
  arg "y" {
    type = char(10)
    mode = OUT
  }
  as = <<-SQL
   BEGIN
    DECLARE перем1 CHAR(10) CHARACTER SET utf8;
    // ...
   END
  SQL
}
```

</TabItem>
<TabItem value="sqlserver" label="SQL Server">

```hcl
procedure "p1" {
  schema = schema.dbo
  as     = <<-SQL
  SET NOCOUNT ON;
  	SELECT [c1], [c2], [c3]
  	FROM [dbo].[t1];
  SQL
}
procedure "p2" {
  schema = schema.dbo
  as     = <<-SQL
  BEGIN
  	SELECT TOP(10) [c1], [c2], [c3] FROM [dbo].[t1];
  	SELECT TOP(10) [c1], [c4] FROM [dbo].[t2]; END
  SQL
}
procedure "p3" {
  schema = schema.dbo
  arg "@c2" {
    type = nvarchar(50)
  }
  arg "@c3" {
    type = nvarchar(50)
  }
  as = <<-SQL
  SET NOCOUNT ON;
  	SELECT [c1], [c2], [c3]
  	FROM [dbo].[t1]
  	WHERE [c2] = @c2 AND [c3] = @c3;
  SQL
}
procedure "p4" {
  schema = schema.dbo
  arg "@c2" {
    type    = nvarchar(50)
    default = "D%"
  }
  arg "@c3" {
    type    = nvarchar(50)
    default = "%"
  }
  as = <<-SQL
  BEGIN
  	SET NOCOUNT ON;
  	SELECT [c1] as [c1], [c2], [c3]
  	FROM [dbo].[t1]
  	WHERE [c2] LIKE @c2 AND [c3] LIKE @c3;
  END
  SQL
}
procedure "p5" {
  schema = schema.dbo
  arg "@a" {
    type = int
  }
  arg "@b" {
    type = int
  }
  arg "@s" {
    type = int
    mode = OUT
  }
  arg "@p" {
    type = int
    mode = OUT
  }
  as = <<-SQL
  SET NOCOUNT ON;
  	SET @s = @a * @b;
  	SET @p = (@a + @b) * 2;
  SQL
}
procedure "p7" {
  schema = schema.dbo
  as     = "TRUNCATE TABLE [dbo].[t1];"
}
procedure "p8" {
  schema = schema.dbo
  arg "@c" {
    type = cursor
    mode = OUT
  }
  as = <<-SQL
  SET NOCOUNT ON;
  	SET @c = CURSOR
  	FORWARD_ONLY STATIC FOR
  	SELECT [c1], [c2]
  	FROM [dbo].[t1];
  	OPEN @c;
  SQL
}
```
</TabItem>
</Tabs>

## Domain

:::info BETA FEATURE
Domains are currently in beta and available to logged-in users only. To use this feature, run:
```
atlas login
```
:::

The `domain` type is a user-defined data type that is based on an existing data type but with optional constraints
and default values. Supported by PostgreSQL.

```hcl
domain "us_postal_code" {
  schema = schema.public
  type   = text
  null   = true
  check "us_postal_code_check" {
    expr = "((VALUE ~ '^\\d{5}$'::text) OR (VALUE ~ '^\\d{5}-\\d{4}$'::text))"
  }
}

domain "username" {
  schema = schema.public
  type    = text
  null    = false
  default = "anonymous"
  check "username_length" {
    expr = "(length(VALUE) > 3)"
  }
}

table "users" {
  schema = schema.public
  column "name" {
    type = domain.username
  }
  column "zip" {
    type = domain.us_postal_code
  }
}

schema "public" {
  comment = "standard public schema"
}
```

## Sequence

:::info BETA FEATURE
Sequences are currently in beta and available to logged-in users only. To use this feature, run:
```
atlas login
```
:::

The `sequence` block allows defining sequence number generator. Supported by PostgreSQL.

Note, a `sequence` block is printed by Atlas on inspection, or it may be manually defined in the schema only if it
represents a PostgreSQL sequence that is not implicitly created by the database for identity or `serial` columns.

```hcl
#highlight-next-line
# Simple sequence with default values.
sequence "s1" {
  schema = schema.public
}

#highlight-next-line
# Sequence with custom configuration.
sequence "s2" {
  schema    = schema.public
  type      = smallint
  start     = 100
  increment = 2
  min_value = 100
  max_value = 1000
}

#highlight-next-line
# Sequence that is owned by a column.
sequence "s3" {
  schema  = schema.public
  owner   = table.t2.column.id
  comment = "Sequence with column owner"
}

#highlight-next-line
# The sequences created by this table are not printed on inspection.
table "users" {
  schema = schema.public
  column "id" {
    type = int
    identity {
        generated = ALWAYS
        start = 10000
    }
  }
  column "serial" {
    type = serial
  }
  primary_key  {
    columns = [column.id]
  }
}

table "t2" {
  schema = schema.public
  column "id" {
    type = int
  }
}

schema "public" {
  comment = "standard public schema"
}
```

## Enum

The `enum` type allows storing a set of enumerated values. Supported by PostgreSQL.

```hcl
enum "status" {
  schema = schema.test
  values = ["on", "off"]
}

table "t1" {
  schema = schema.test
  column "c1" {
    type = enum.status
  }
}

table "t2" {
  schema = schema.test
  column "c1" {
    type = enum.status
  }
}
```

## Comment

The `comment` attribute is an attribute of `schema`, `table`, `column`, and `index`.

```hcl
schema "public" {
  comment = "A schema comment"
}

table "users" {
  schema = schema.public
  column "name" {
    type    = text
    comment = "A column comment"
  }
  index "name_idx" {
    columns = [column.name]
  }
  comment = "A table comment"
}
```

## Charset and Collation

The `charset` and `collate` are attributes of `schema`, `table` and `column` and supported by MySQL, MariaDB and PostgreSQL.
Read more about them in [MySQL](https://dev.mysql.com/doc/refman/8.0/en/charset.html),
[MariaDB](https://mariadb.com/kb/en/setting-character-sets-and-collations/) and
[PostgreSQL](https://www.postgresql.org/docs/current/collation.html) websites.

<Tabs defaultValue="mysql">
<TabItem label="MySQL" value="mysql">

```hcl
schema "public" {
    charset = "utf8mb4"
    collate = "utf8mb4_0900_ai_ci"
}

table "products" {
    column "name" {
        type    = text
        collate = "binary"
    }
    collate = "utf8_general_ci"
}
```

</TabItem>
<TabItem label="PostgreSQL" value="postgres">

```hcl
schema "public" {}

table "products" {
  column "name" {
    type    = text
    collate = "es_ES"
  }
}
```

</TabItem>
<TabItem value="sqlserver" label="SQL Server">

SQLServer only support `collate` attribute on columns.
```hcl
schema "dbo" {}

table "users" {
  schema = schema.dbo
  column "name" {
    type    = varchar(255)
    collate = "Vietnamese_CI_AS"
  }
}
```

</TabItem>
</Tabs>

## Auto Increment

`AUTO_INCREMENT` and `IDENTITY` columns are attributes of the `column` and `table` resource, and can be used to
generate a unique identity for new rows.


<Tabs defaultValue="mysql">
<TabItem label="MySQL" value="mysql">

In MySQL/MariaDB the `auto_increment` attribute can be set on columns and tables.

```hcl
table "users" {
  schema = schema.public
  column "id" {
    null = false
    type = bigint
    auto_increment = true
  }
  primary_key  {
    columns = [column.id]
  }
}
```

The `auto_increment` column can be set on the table to configure a start value other than 1.

```hcl
table "users" {
  schema = schema.public
  column "id" {
    null = false
    type = bigint
    auto_increment = true
  }
  primary_key  {
    columns = [column.id]
  }
  auto_increment = 100
}
```

</TabItem>
<TabItem label="PostgreSQL" value="postgres">

PostgreSQL supports `serial` columns and the `generated as identity` syntax for versions >= 10.

```hcl
table "users" {
  schema = schema.public
  column "id" {
    null = false
    type = int
    identity {
        generated = ALWAYS
        start = 10
        increment = 10
    }
  }
  primary_key  {
    columns = [column.id]
  }
}
```

</TabItem>
<TabItem label="SQLite" value="sqlite">

SQLite allows configuring [`AUTOINCREMENT`](https://www.sqlite.org/autoinc.html) columns using the `auto_increment`
attribute.

```hcl
table "users" {
  schema = schema.main
  column "id" {
    null = false
    type = integer
    auto_increment = true
  }
  primary_key  {
    columns = [column.id]
  }
}
```

</TabItem>
<TabItem value="sqlserver" label="SQL Server">

```hcl
table "users" {
  schema = schema.dbo
  column "id" {
    null = false
    type = bigint
    identity {
      seed      = 701
      increment = 1000
    }
  }
  primary_key  {
    columns = [column.id]
  }
}
```

</TabItem>
</Tabs>
